﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Homework8
{
    public class DBHelper
    {
        private static readonly string ConnectionString = "server=localhost;user id=root;password=123456;database=Homework8;charset=utf8;Convert Zero Datetime=True;Allow Zero Datetime=True;";

        public static MySqlConnection GetConnection()
        {
            return new MySqlConnection(ConnectionString);
        }

        public static void InitializeDatabase()
        {
            try
            {
                // 先创建数据库
                string initConnectionString = "server=localhost;user id=root;password=123456;";
                using var initConnection = new MySqlConnection(initConnectionString);
                initConnection.Open();
                
                string createDbSql = "CREATE DATABASE IF NOT EXISTS Homework8;";
                using var createDbCommand = new MySqlCommand(createDbSql, initConnection);
                createDbCommand.ExecuteNonQuery();
                
                // 再连接到数据库执行初始化脚本
                using var connection = GetConnection();
                connection.Open();
                
                // 读取SQL脚本文件
                string sqlFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "InitDatabase.sql");
                Console.WriteLine($"尝试读取SQL文件: {sqlFilePath}");
                
                if (!File.Exists(sqlFilePath))
                {
                    throw new FileNotFoundException($"找不到SQL脚本文件: {sqlFilePath}");
                }
                
                string sqlScript = File.ReadAllText(sqlFilePath);
                
                // 移除创建数据库和USE语句，因为已经在连接字符串中指定了数据库
                sqlScript = sqlScript.Replace("# 创建数据库\r\nCREATE DATABASE IF NOT EXISTS Homework8;\r\n\r\n# 使用数据库\r\nUSE Homework8;\r\n\r\n", "");
                sqlScript = sqlScript.Replace("# 创建数据库\nCREATE DATABASE IF NOT EXISTS Homework8;\n\n# 使用数据库\nUSE Homework8;\n\n", "");
                
                // 手动执行各个部分的SQL语句
                ExecuteCreateTableStatements(connection);
                ExecuteInsertDataStatements(connection);
                
                Console.WriteLine("数据库初始化成功");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"数据库初始化错误: {ex.Message}");
                throw;
            }
        }
        
        private static void ExecuteCreateTableStatements(MySqlConnection connection)
        {
            Console.WriteLine("创建表结构...");
            
            // 创建学校表
            string createSchoolTable = @"
            CREATE TABLE IF NOT EXISTS School (
                SchoolId INT AUTO_INCREMENT PRIMARY KEY,
                Name VARCHAR(100) NOT NULL,
                Address VARCHAR(200),
                FoundingYear INT
            );";
            
            ExecuteSqlStatement(connection, createSchoolTable);
            
            // 创建班级表
            string createClassTable = @"
            CREATE TABLE IF NOT EXISTS Class (
                ClassId INT AUTO_INCREMENT PRIMARY KEY,
                Name VARCHAR(50) NOT NULL,
                Grade INT,
                SchoolId INT,
                ClassTeacher VARCHAR(50),
                FOREIGN KEY (SchoolId) REFERENCES School(SchoolId)
            );";
            
            ExecuteSqlStatement(connection, createClassTable);
            
            // 创建学生表
            string createStudentTable = @"
            CREATE TABLE IF NOT EXISTS Student (
                StudentId INT AUTO_INCREMENT PRIMARY KEY,
                Name VARCHAR(50) NOT NULL,
                Age INT,
                Gender VARCHAR(10),
                ClassId INT,
                EnrollmentDate VARCHAR(20),
                Address VARCHAR(200),
                FOREIGN KEY (ClassId) REFERENCES Class(ClassId)
            );";
            
            ExecuteSqlStatement(connection, createStudentTable);
            
            // 创建日志表
            string createLogTable = @"
            CREATE TABLE IF NOT EXISTS Log (
                LogId INT AUTO_INCREMENT PRIMARY KEY,
                Operation VARCHAR(20) NOT NULL,
                TableName VARCHAR(50) NOT NULL,
                RecordId INT,
                Description VARCHAR(500),
                OperationTime VARCHAR(20) NOT NULL
            );";
            
            ExecuteSqlStatement(connection, createLogTable);
        }
        
        private static void ExecuteInsertDataStatements(MySqlConnection connection)
        {
            Console.WriteLine("插入初始数据...");
            
            // 插入学校数据
            string insertSchoolData = @"
            INSERT INTO School (Name, Address, FoundingYear) VALUES 
            ('文昌中学', '湖北省孝感市', 1999),
            ('丹阳中学', '湖北省孝感市', 1999);";
            
            ExecuteSqlStatement(connection, insertSchoolData);
            
            // 插入班级数据
            string insertClassData = @"
            INSERT INTO Class (Name, Grade, SchoolId, ClassTeacher) VALUES 
            ('初一(1)班', 7, 1, '张老师'),
            ('初一(2)班', 7, 1, '李老师'),
            ('初一(1)班', 7, 2, '王老师');";
            
            ExecuteSqlStatement(connection, insertClassData);
            
            // 插入学生数据
            string insertStudentData = @"
            INSERT INTO Student (Name, Age, Gender, ClassId, EnrollmentDate, Address) VALUES 
            ('张三', 16, '男', 1, '2025-05-31', '湖北省恩施'),
            ('李四', 16, '女', 1, '2025-05-31', '湖北省武汉市'),
            ('王五', 17, '男', 2, '2025-05-31', '湖北省孝感市');";
            
            ExecuteSqlStatement(connection, insertStudentData);
        }
        
        private static void ExecuteSqlStatement(MySqlConnection connection, string sql)
        {
            try
            {
                using var command = new MySqlCommand(sql, connection);
                command.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"执行SQL语句失败: {ex.Message}");
                Console.WriteLine($"SQL: {sql}");
                throw;
            }
        }

        public static void LogOperation(string operation, string tableName, int? recordId, string description)
        {
            using var connection = GetConnection();
            connection.Open();
            
            string sql = "INSERT INTO Log (Operation, TableName, RecordId, Description, OperationTime) VALUES (@Operation, @TableName, @RecordId, @Description, @OperationTime)";
            
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@Operation", operation);
            command.Parameters.AddWithValue("@TableName", tableName);
            command.Parameters.AddWithValue("@RecordId", recordId.HasValue ? (object)recordId.Value : DBNull.Value);
            command.Parameters.AddWithValue("@Description", description ?? string.Empty);
            command.Parameters.AddWithValue("@OperationTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            
            command.ExecuteNonQuery();
        }
    }
}
